import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.AreaReference;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class ExportUtils {

    private static  final  int PAGE  = 5;

    public void export(){
        String template = "D:/file/temp/sample.xls";
        String excelFileName = "D:/file/test/test.xls";
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(template);
            POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream);
            HSSFWorkbook wb = new HSSFWorkbook(fileSystem);
            OutputStream outputStream = new FileOutputStream(excelFileName);
            for(int i = 0;i<PAGE;i++){
                wb.createSheet("第"+(i+2)+"页");
            }

            List<String> titles = getConfigRows(wb,0);

            List<String> listTitles = getConfigRows(wb,1);

            List<String> conf = getConfigRows(wb,2);

            for (String title:titles){
                POIUtils.setCellValueByAlias(wb,title,"value");
            }
            setData(wb,wb.getSheetAt(1));

            CopyUtils copyUtils = new CopyUtils();
            try {
                for (int i = 0 ; i<PAGE;i++){
                    HSSFSheet sheet = wb.getSheetAt(i + 2);
                    copyUtils.copySheet(wb.getSheetAt(1),sheet,wb,wb);
                    for (String title:titles){
                        POIUtils.setCellValueByAlias(wb,sheet,title,"value",0);
                    }
                    setData(wb,sheet);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            wb.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    private void setData(HSSFWorkbook wb,HSSFSheet sheet){
        Name name = wb.getName("一览行");
        AreaReference cellReference = new AreaReference(name.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
        int row = cellReference.getFirstCell().getRow();
        int index = wb.getSheetIndex(sheet);
        int end = 3;
        if(index > 1){
            row = row + 3;
            end = end - 3;
        }
        POIUtils.insertRow(wb,sheet,
                   row, end);
        for(int i=0;i<4;i++){
           POIUtils.setCellValueByAlias(wb,sheet,"id","xxx"+i,i);
           POIUtils.setCellValueByAlias(wb,sheet,"员工姓名",sheet.getSheetName(),i);
           POIUtils.setCellValueByAlias(wb,sheet,"员工编号","xxx",i);
        }
    }

    private List<String> getConfigRows(HSSFWorkbook wb,int cellNo) {
        List<String> names = new ArrayList<String>();
        HSSFSheet sheet = wb.getSheetAt(0);
        int r = 0;
        HSSFRow row = sheet.getRow(r);
        if(row==null){
            return names;
        }
        while ((row = sheet.getRow(r))!=null){
            HSSFCell cell = row.getCell(cellNo);
            if(r==1 && cellNo == 2){
                Double dbNum = cell.getNumericCellValue();
                Integer intNo = dbNum.intValue();
                String value = intNo.toString();
                names.add(value);
            }else{
                String value = cell.getStringCellValue();
                names.add(value);
            }
            r++;
        }
        return names;
    }

}
